﻿

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[balcon_ImportAnterioara]
	-- Add the parameters for the stored procedure here
	@ServerName varchar(50), 
	@DBName varchar(50),
	@CodUnitate smallint,
	@Versiune smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @comanda varchar(5000)
    DECLARE @strCodUnitate varchar(10)
	SET @strCodUnitate = cast(@CodUnitate as varchar(10))
	
	--import unitate---------------------
	DECLARE @cateunit int
	BEGIN TRAN
	SELECT @cateunit = COUNT(*) FROM [Unitati] WHERE [CodUnitate]=@CodUnitate
	
	IF @cateunit = 0
	BEGIN
		SET IDENTITY_INSERT [Unitati] ON	
		SET @Comanda = 'INSERT INTO [Unitati] ([CodUnitate],[Denumire],[UnitateSursa]'
		IF @Versiune > 1
			SET @Comanda = @comanda + 	',[CUI],[AtributFiscal],[Adresa],[IBAN],[IDBanca],[AgentieBancara]'
		SET @Comanda = @comanda +  ') SELECT  [CodUnitate],[Denumire],[UnitateSursa]'
		IF @Versiune > 1
			SET @Comanda = @comanda + 	',[CUI],[AtributFiscal],[Adresa],[IBAN],[IDBanca],[AgentieBancara]'
		SET @Comanda = @comanda +  ' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Unitati] WHERE [CodUnitate]=' +@strCodUnitate   		
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET IDENTITY_INSERT [Unitati] OFF	
	END 
	---end import unitate-----------------------
		
	---import arhive
	
	SET IDENTITY_INSERT [Arhive] ON	
	SET @Comanda = 'INSERT INTO [Arhive] (
	[CodArhiva],[Denumire],[CodUnitate],[DataInceput],[DataSfarsit],[Anul])
	  SELECT [CodArhiva],[Denumire],[CodUnitate],[DataInceput],[DataSfarsit],[Anul] 
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Arhive] WHERE [CodUnitate]=' +@strCodUnitate   		
	--PRINT(@Comanda)
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Arhive] OFF		
	--preupunem ca baza de date nu contine nici o arhiva
	DECLARE @anArhiva smallint
	DECLARE @strAnArhiva varchar(4)
	
	DECLARE  crsSpor SCROLL CURSOR FOR 
		SELECT [Anul] FROM [Arhive] 
		WHERE  [CodUnitate]=@CodUnitate 
	
	OPEN crsSpor
	FETCH first from  crsSpor INTO @anArhiva

	WHILE @@fetch_status = 0  
	BEGIN
		
		SET @strAnArhiva = CAST(@AnArhiva as varchar(4))
		
		SET @Comanda = 'SELECT * INTO [JurnaleSolduriInitiale'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[JurnaleSolduriInitiale'+ @strAnArhiva +']' 
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		PRINT('JurnaleSolduriInitiale - OK')
		
		SET @Comanda = 'SELECT *  INTO [Bilant'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Bilant'+ @strAnArhiva +']'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'SELECT *  INTO [Conturi'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Conturi'+ @strAnArhiva +']'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		
		SET @Comanda = 'SELECT *  INTO [ConturiValuta'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[ConturiValuta'+ @strAnArhiva +']'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE			
		
		SET @Comanda = 'SELECT *  INTO [RezultatPatrimonial'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[RezultatPatrimonial'+ @strAnArhiva +']'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		
		SET @Comanda = 'SELECT *  INTO [RulajeDoc'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[RulajeDoc'+ @strAnArhiva +']'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		
		SET @Comanda = 'SELECT *  INTO [Rulaje'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Rulaje'+ @strAnArhiva +']'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		
		SET @Comanda = 'SELECT * INTO [VenituriSiCheltuieli'+ @strAnArhiva +'] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[VenituriSiCheltuieli'+ @strAnArhiva +']'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		FETCH NEXT from crsSpor INTO @anArhiva
	END
	CLOSE crsSpor
	DEALLOCATE crsSpor
	---end import arhive	
	PRINT 'Arhive importate cu succes'
		
	SET IDENTITY_INSERT [Bilant] ON
	SET @Comanda = 'INSERT INTO [Bilant](
	[row],[nr_rand],[text],[codfix],[cod],[coltypei],[soldi],[coltypef],[soldf],[eval],[CodUnitate],[Boldat])' +
      ' SELECT [row],[nr_rand],[text],[codfix],[cod],[coltypei],[soldi],[coltypef],[soldf],[eval],[CodUnitate],[Boldat] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[Bilant] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Bilant] OFF
	PRINT('Import Bilant - ok')	
	
	SET @Comanda = 'INSERT INTO [Conturi] (
	[Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold])' +
      ' SELECT [Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[Conturi] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Import Conturi - ok')
	

	SET @Comanda = 'INSERT INTO [ConturiCoresp](
	[ContDebit],[ContCredit],[IdJurnal],[CodUnitate],[Tip])' +
      ' SELECT [ContDebit],[ContCredit],[IdJurnal],[CodUnitate],[Tip] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[ConturiCoresp] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Import ConturiCoresp - ok')

	SET @Comanda = 'INSERT INTO [ConturiSpeciale](
	[Cont],[CodUnitate],[Tipuri],[Descriere])' +
      ' SELECT [Cont],[CodUnitate],[Tipuri],[Descriere] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[ConturiSpeciale] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Import ConturiSpeciale - ok')

	SET IDENTITY_INSERT [jurnale] ON
	SET @Comanda = 'INSERT INTO [Jurnale]([IDJurnal],[Denumire],[IDParinte],[Codificare],[Terminal],[CodUnitate]'
	IF @Versiune >1 
		SET @Comanda = @comanda + ',[IBAN],[IDBanca]' 
    SET @Comanda = @comanda + ') SELECT [IDJurnal],[Denumire],[IDParinte],[Codificare],[Terminal],[CodUnitate]'
    IF @Versiune >1 
		SET @Comanda = @comanda + ',[IBAN],[IDBanca]'
	 SET @Comanda = @comanda + ' FROM ['+@ServerName+'].['+ @DBName+'].[dbo].[Jurnale] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [jurnale] OFF
	PRINT('Import jurnale - ok')

	--SET IDENTITY_INSERT [ParamListe] ON
	SET @Comanda = 'INSERT INTO [ParamListe]([IDLista],[Cod],[NrCrt],[Denumire],[Valoare],[ValoareImplicita],[Tip],[Validare],[SelRezumat],[User],[Host],[CodUnitate])' +
      ' SELECT [IDLista],[Cod],[NrCrt],[Denumire],[Valoare],[ValoareImplicita],[Tip],[Validare],[SelRezumat],[User],[Host],[CodUnitate] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[ParamListe] WHERE [CodUnitate]=' +@strCodUnitate   
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	--SET IDENTITY_INSERT [ParamListe] OFF
	PRINT('Import ParamListe - ok')

	SET IDENTITY_INSERT [RezultatPatrimonial] ON
	SET @Comanda = 'INSERT INTO [RezultatPatrimonial](
	[row],[nr_rand],[text],[codfix],[cod],[coltypei],[soldi],[coltypef],[soldf],[eval],[CodUnitate],[Boldat])' +
      ' SELECT [row],[nr_rand],[text],[codfix],[cod],[coltypei],[soldi],[coltypef],[soldf],[eval],[CodUnitate],[Boldat] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[RezultatPatrimonial] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [RezultatPatrimonial] OFF
	PRINT('Import RezultatPatrimonial - ok')

	SET IDENTITY_INSERT [RulajeDoc] ON
	SET @Comanda = 'INSERT INTO  [RulajeDoc](
	[IDDocument],[ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal],[Observatii],[xUser],[xHost],[xDate],[CodUnitate])' +
      ' SELECT [IDDocument],[ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal],[Observatii],[xUser],[xHost],[xDate],[CodUnitate] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[RulajeDoc] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [RulajeDoc] OFF
	PRINT('Import RulajeDoc - ok')

	SET IDENTITY_INSERT [Rulaje] ON
	SET @Comanda = 'INSERT INTO [Rulaje](
	[CodFormula],[ContDebit],[ContCredit],[CodValuta],[IDDocument],[ValoareValuta],[Curs],[ValoareLei],[DataDocJust],[TipDocJust],[NumarDocJust],[ExplicatiiDocJust],[NrCrt],[CodUnitate])' +
      ' SELECT [CodFormula],[ContDebit],[ContCredit],[CodValuta],[IDDocument],[ValoareValuta],[Curs],[ValoareLei],[DataDocJust],[TipDocJust],[NumarDocJust],[ExplicatiiDocJust],[NrCrt],[CodUnitate] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[Rulaje] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Rulaje] OFF
	
	PRINT('Import Rulaje - ok')

	SET @Comanda = 'INSERT INTO [SeriiNumerice](
	[IdSerie],[CodUnitate],[Descriere],[NrStart],[NrEnd],[UltimaDataFolosit],[UltimulNrFolosit],[NumarImplicit],[NumarManual])' +
      ' SELECT [IdSerie],[CodUnitate],[Descriere],[NrStart],[NrEnd],[UltimaDataFolosit],[UltimulNrFolosit],[NumarImplicit],[NumarManual] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[SeriiNumerice] WHERE [CodUnitate]=' +@strCodUnitate   
    
    PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Import SeriiNumerice - ok')

	SET IDENTITY_INSERT [VenituriSicheltuieli] ON
	SET @Comanda = 'INSERT INTO [VenituriSiCheltuieli](
	[row],[nr_rand],[text],[codfix],[cod],[coltypei],[soldi],[coltypef],[soldf],[eval],[CodUnitate],[Boldat])' +
      ' SELECT [row],[nr_rand],[text],[codfix],[cod],[coltypei],[soldi],[coltypef],[soldf],[eval],[CodUnitate],[Boldat] FROM ['+@ServerName+'].['+
      @DBName+'].[dbo].[VenituriSiCheltuieli] WHERE [CodUnitate]=' +@strCodUnitate   
    
    PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [VenituriSicheltuieli] OFF
	PRINT('Import VenituriSiCheltuieli - ok')

	SET @Comanda = 'INSERT INTO [BalconSetup](
	[PrimaryKey],[CodUnitate],[TipUnitate],[FolosesteNota],[FolosesteJurnal],[FolosesteObservatii],[DocumentJustifRulaj],[IdSerieNC],[FolosesteValute],[Linia1Stanga],[Linia1Dreapta],[Linia2Stanga],[Linia2Dreapta],[Linia3Stanga],[Linia3Dreapta],[AnCurent],[LunaCurenta],[SoldPrecedentActiv],[CopiazaExplicatia],[DataBlocareStart],[FaraAdaugareGrid],[SubsolStanga1],[SubsolCentru1],[SubsolDreapta1],[SubsolStanga2],[SubsolCentru2],[SubsolDreapta2]'
	IF @versiune>1
		SET @Comanda = @comanda + ',[SubsolNume3],[SubsolSemnatura3],[NoteInchidereUnice]' 
	SET @Comanda = @comanda +    ') SELECT [PrimaryKey],[CodUnitate],[TipUnitate],[FolosesteNota],[FolosesteJurnal],[FolosesteObservatii],[DocumentJustifRulaj],[IdSerieNC],[FolosesteValute],[Linia1Stanga],[Linia1Dreapta],[Linia2Stanga],[Linia2Dreapta],[Linia3Stanga],[Linia3Dreapta],[AnCurent],[LunaCurenta],[SoldPrecedentActiv],[CopiazaExplicatia],[DataBlocareStart],[FaraAdaugareGrid],[SubsolStanga1],[SubsolCentru1],[SubsolDreapta1],[SubsolStanga2],[SubsolCentru2],[SubsolDreapta2]'
	IF @versiune>1
		SET @Comanda = @comanda + ',[SubsolNume3],[SubsolSemnatura3],[NoteInchidereUnice]'
	SET @Comanda = @comanda + '  FROM ['+@ServerName+'].['+ @DBName+'].[dbo].[BalconSetup] WHERE [CodUnitate]=' +@strCodUnitate   
    
    --PRINT(@Comanda) 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Import Balcon - ok')

 	COMMIT



	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		CLOSE crsSpor
		DEALLOCATE crsSpor
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END